package my.myorm.tool.jdbc;


import my.tool.ConfigProp;
import my.myspring.tool.SpringBeanProxy;

import javax.sql.DataSource;
import java.sql.*;
import java.util.List;
import java.util.LinkedList;
import java.text.SimpleDateFormat;
import org.apache.commons.logging.LogFactory;
import org.apache.commons.logging.Log;

/**
 * sql执行器，所有的sql都在此处执行
 *
 * @author Skeven Huang
 * @version 1.0
 */
public class SQLExecutor{
    private int maxrows = 100000;
    private int query_time_out_sec = 30;
    private Connection conn = null;
    private List params = new LinkedList();
    private List<String> repls = new LinkedList<String>();
    private PreparedStatement prepStatement = null;
    private boolean isCreateConnOnNew = false;
    private boolean isAllreadyInserted = false;
    private boolean isPageQueryed = false;
    private ResultSetHandler resultSetHandler=null;
    private PageQueryer queryer=null;

    private String dsName="DefaultDataSource"; 

    /**
     * 默认为true，若为true，则没一次执行都会打印出sql语句
     */

    private static DataSourceFactory dataSourceFactory = null;
    private Log logger= LogFactory.getLog("SQL.LOG");
    private Log logger_select= LogFactory.getLog("SQL.SELECT");

    static {
        try {
            String ds = ConfigProp.getProperties("my.orm.datasource");
            SQLExecutor.setDataSourceFactory((DataSourceFactory) Class.forName(ds).newInstance());
            System.out.println("****************** init myorm datasource... ok ******************");
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("****************** init myorm datasource error!!!");
        }
    }

    private void createConn(boolean autoCommit) throws Exception {
        //DataSource datasource = dataSourceFactory.getDataSource();
        DataSource datasource = (DataSource) SpringBeanProxy.getBean(this.dsName);
        if (datasource == null) {
            throw new Exception("datasource==null");
        }
        conn = datasource.getConnection();
        conn.setAutoCommit(autoCommit);
        String databaseProductName=conn.getMetaData().getDatabaseProductName();
        resultSetHandler=ResultSetHandlerFactory.getInstance().getResultSetHandler(databaseProductName);
        queryer=PageQueryerFactory.getInstance().getPageQueryer(databaseProductName);
    }

    protected SQLExecutor(boolean isCreateConnOnNew) throws Exception {
        if (isCreateConnOnNew) {
            createConn(false);
        }
        this.isCreateConnOnNew = isCreateConnOnNew;
    }

    protected SQLExecutor() throws Exception {
        this(false);
    }

    protected SQLExecutor(String dsname) throws Exception {
        if(dsname!=null&&!"".equals(dsname)) this.dsName=dsname;
    }

    /**
     * 添加参数
     *
     * @param param
     */
    public void addParam(Object param) {
        params.add(param);
    }

    public void addRepl(String param) {
        repls.add(param);
    }

    /**
     * 等同于execute(sql, false)
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public long execute(String sql) throws Exception {
        return execute(sql, false);
    }

    /**
     * 执行增删改sql,若是插入语句并且isAutoIncr=true,则返回自增一字段的值，否则返回受影响的行数
     *
     * @param sql
     * @param isAutoIncr 对于insert是否需要返回自增一字段的值
     * @return
     * @throws Exception
     */
    public long execute(String sql, boolean isAutoIncr) throws Exception {
        try {
            if (!isCreateConnOnNew) {
                createConn(true);
            }
            createPrepStatement(sql, true);
            long result = prepStatement.executeUpdate();
            if (isInsert(sql) && isAutoIncr) {//返回自增一ID
                isAllreadyInserted = true;
                sql = "select @@identity as id";
                result = queryForBean(sql, DefaultBean.class, true, "").getLong("id");
                isAllreadyInserted = false;
            }
            if (isCreateConnOnNew) {
                commit();
            }
            return result;
        } catch (Exception ex) {
            if (isCreateConnOnNew) {
                rollBack();
            }
            throw ex;
        } finally {
            if (!isCreateConnOnNew) {
                close();
            }
        }
    }
    
    /**
     * 执行sql查询并转换
     *
     * @param sql
     * @param isAll 是否需要将所有的字段和值在DefaultBean中备份一份，如果没这个需要建议为false，节省系统开销
     * @param pages 分页对象
     * @return
     * @throws Exception
     */
    public ResultList query(String sql, boolean isAll, Pages pages,int count,String unionTbl) throws Exception {
        return query(sql, isAll, pages, true,count,unionTbl);
    }

    /**
     * 执行sql查询并转换
     *
     * @param sql
     * @param isAll 是否需要将所有的字段和值在DefaultBean中备份一份，如果没这个需要建议为false，节省系统开销
     * @param pages 分页对象
     * @return
     * @throws Exception
     */
    
    public ResultList query(String sql, boolean isAll, Pages pages) throws Exception {
        return query(sql, isAll, pages, true, null);
    }

    
    public ResultList query(String sql, boolean isAll, Pages pages, String unionTbl) throws Exception {
        return query(sql, isAll, pages, true, unionTbl);
    }

    /**
     * 执行query，返回resultlist的第一个记录
     *
     * @param sql
     * @param isAll
     * @return
     * @throws Exception
     */
    public RecordMap queryForMap(String sql, boolean isAll) throws Exception {
        return queryForMap(sql, isAll, true);
    }

    /**
     * 执行queryForMap，返回RecordMap中的指定javabean
     *
     * @param sql
     * @param t
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> T queryForBean(String sql, Class<T> t) throws Exception {
        return queryForBean(sql, t, "");
    }

    /**
     * 执行queryForMap，根据表别名返回RecordMap中的指定javabean
     *
     * @param sql
     * @param t
     * @param tableAlia
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> T queryForBean(String sql, Class<T> t, String tableAlia) throws Exception {
        return queryForBean(sql, t, true, tableAlia);
    }
    
    
    public ResultList querySpecCount(String sql, boolean isAll, Pages pages, boolean clearParams,String unionTbl) throws Exception {
        if (pages == null || !sql.trim().toLowerCase().startsWith("select ") || sql.toLowerCase().indexOf(" from ") <= 0 || (pages.getRequest() == null && pages.getCurPage() == -1)) {
       
        	try {
                if (!isCreateConnOnNew && !isAllreadyInserted && !isPageQueryed) {
                    createConn(true);
                }
                createPrepStatement(sql, clearParams);
                ResultSet rs = prepStatement.executeQuery();
              
                ResultList list = resultSetHandler.handleResult(rs, isAll,unionTbl);
               
                try {
                    rs.close();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
                return list;
            } catch (Exception e) {
                throw e;
            }
            finally {
                if (!isCreateConnOnNew && !isAllreadyInserted && !isPageQueryed) {
                    close();
                }
            }

        } else {
            try {
                if (!isCreateConnOnNew) {
                    createConn(true);
                }
                //
          
                String countsql= "select * from(" + sql.replaceAll("\\*", "count(\\*)") ;
                countsql = countsql.substring(0,countsql.lastIndexOf("order by"));
                countsql += ") as counts";
             
                
                int allCount = 0;
      
                
                createPrepStatement(countsql, clearParams);
                ResultSet rs = prepStatement.executeQuery();
                
                while(rs.next()){ 
                	allCount += rs.getInt(1);
                } 
                

                isPageQueryed = true;
                int[] aintPage = getPageRecordsNumber(allCount, pages.getCurPage() != -1 ? String.valueOf(pages.getCurPage()) : pages.getRequest().getParameter("ipage"), null, pages.getPageSize());
                String pagesql=queryer.getSqlForPage(sql,aintPage[1],aintPage[2]);
               
                ResultList result = query(pagesql, isAll, null, true,unionTbl);
                result.setAllCount(allCount);
             //   System.out.println("setAllCount:" + allCount);
                isPageQueryed = false;
                return result;
            } catch (Exception ex) {
                throw ex;
            } finally {
                if (!isCreateConnOnNew) {
                    close();
                }
            }
        }
    }

    private ResultList query(String sql, boolean isAll, Pages pages, boolean clearParams,String unionTbl) throws Exception {
        if (pages == null || !sql.trim().toLowerCase().startsWith("select ") || sql.toLowerCase().indexOf(" from ") <= 0 || (pages.getRequest() == null && pages.getCurPage() == -1)) {
            
      
        	
        	try {
                if (!isCreateConnOnNew && !isAllreadyInserted && !isPageQueryed) {
                    createConn(true);
                }
                createPrepStatement(sql, clearParams);
                ResultSet rs = prepStatement.executeQuery();
                //long begin=System.currentTimeMillis();
                ResultList list = resultSetHandler.handleResult(rs, isAll,unionTbl);
                //System.out.println(list.size()+":"+(System.currentTimeMillis()-begin)+"ms");
                try {
                    rs.close();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
                return list;
            } catch (Exception e) {
                throw e;
            }
            finally {
                if (!isCreateConnOnNew && !isAllreadyInserted && !isPageQueryed) {
                    close();
                }
            }

        } else {
            try {
                if (!isCreateConnOnNew) {
                    createConn(true);
                }
                String countsql="select count(" + pages.getCountField() + ") as totalcount "+queryer.getSuffixSqlForTotal(sql);
                //2010-10-22,hxp
                /*
                sql = sql.trim();
                String newsql = "select count(" + pages.getCountField() + ") as totalcount " +
                        (sql.toLowerCase().indexOf(" order by ") > 0 ?
                                sql.substring(sql.toLowerCase().indexOf(" from "), sql.toLowerCase().indexOf(" order by ")) :
                                sql.substring(sql.toLowerCase().indexOf(" from "))
                        );
                if (sql.toLowerCase().indexOf("group by") >= 0) {
                    newsql = "select count(" + pages.getCountField() + ") as totalcount from (" +
                            (sql.toLowerCase().indexOf(" order by ") > 0 ?
                                    sql.substring(0, sql.toLowerCase().indexOf(" order by ")) :
                                    sql) + " ) as a";
                }*/
                isPageQueryed = true;
                int allCount = queryForBean(countsql, DefaultBean.class, false, "").getInt("totalcount");
                int[] aintPage = getPageRecordsNumber(allCount, pages.getCurPage() != -1 ? String.valueOf(pages.getCurPage()) : pages.getRequest().getParameter("ipage"), null, pages.getPageSize());
                String pagesql=queryer.getSqlForPage(sql,aintPage[1],aintPage[2]);
                //2010-10-22,hxp
                /*sql = (sql.toLowerCase().indexOf(" limit ") > 0 ?
                        sql.substring(0, sql.toLowerCase().indexOf(" limit ")) : sql)
                        + " limit " + String.valueOf(aintPage[1]) + "," + String.valueOf(aintPage[2]);*/
                ResultList result = query(pagesql, isAll, null, true,unionTbl);
                result.setAllCount(allCount);
           //     System.out.println("setAllCount:" + allCount);
                isPageQueryed = false;
                return result;
            } catch (Exception ex) {
                throw ex;
            } finally {
                if (!isCreateConnOnNew) {
                    close();
                }
            }
        }
    }
    
    private ResultList query(String sql, boolean isAll, Pages pages, boolean clearParams,int count,String unionTbl) throws Exception {
        if (pages == null || !sql.trim().toLowerCase().startsWith("select ") || sql.toLowerCase().indexOf(" from ") <= 0 || (pages.getRequest() == null && pages.getCurPage() == -1)) {
            try {
                if (!isCreateConnOnNew && !isAllreadyInserted && !isPageQueryed) {
                    createConn(true);
                }
                createPrepStatement(sql, clearParams);
                ResultSet rs = prepStatement.executeQuery();
                //long begin=System.currentTimeMillis();
                ResultList list = resultSetHandler.handleResult(rs, isAll,unionTbl);
                //System.out.println(list.size()+":"+(System.currentTimeMillis()-begin)+"ms");
                try {
                    rs.close();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
                return list;
            } catch (Exception e) {
                throw e;
            }
            finally {
                if (!isCreateConnOnNew && !isAllreadyInserted && !isPageQueryed) {
                    close();
                }
            }

        } else {
            try {
                if (!isCreateConnOnNew) {
                    createConn(true);
                }
                isPageQueryed = true;
            	int allCount = count;
            	if(allCount<=0){
	                String countsql="select count(" + pages.getCountField() + ") as totalcount "+queryer.getSuffixSqlForTotal(sql);
	                allCount = queryForBean(countsql, DefaultBean.class, false, "").getInt("totalcount");
            	}
                int[] aintPage = getPageRecordsNumber(allCount, pages.getCurPage() != -1 ? String.valueOf(pages.getCurPage()) : pages.getRequest().getParameter("ipage"), null, pages.getPageSize());
                String pagesql=queryer.getSqlForPage(sql,aintPage[1],aintPage[2]);
                ResultList result = query(pagesql, isAll, null, true,unionTbl);
                result.setAllCount(allCount);
                isPageQueryed = false;
                return result;
            } catch (Exception ex) {
                throw ex;
            } finally {
                if (!isCreateConnOnNew) {
                    close();
                }
            }
        }
    }

    private RecordMap queryForMap(String sql, boolean isAll, boolean clearParams) throws Exception {
        ResultList list = query(sql, isAll, null, clearParams,null);
        if (list.size() == 0) return null;
        return list.get(0);
    }

    private <T> T queryForBean(String sql, Class<T> t, boolean clearParams, String tableAlia) throws Exception {
        RecordMap first = queryForMap(sql, false, clearParams);
        if (first == null) return null;
        return first.get(t, tableAlia);
    }

    protected void close() throws Exception {
        if (conn != null) {
            conn.close();
        }

    }

    protected void commit() throws Exception {
        if (conn != null)
            conn.commit();
    }

    protected void rollBack() throws Exception {
        if (conn != null)
            conn.rollback();
    }


    private void createPrepStatement(String sql, boolean clearParams) throws Exception {
    
    	//静态替换
        for(int i=0;i<repls.size();i++){
            sql=replace(sql,":?",repls.get(i),-1);
        }
        
        String qrepl = "@@@@@@NG,，m2076v##_1lnzzx##@@@";
        String cqrepl = "m2076v@@@@@@NG,，##_1lnzzx##2076v";
        String newSql = replace(sql, "\\?", cqrepl, -1).trim();
        newSql = replace(newSql, "?", qrepl, -1);
        prepStatement = conn.prepareStatement(sql);
        for (int i = 0; i < params.size(); i++) {
            Object param = params.get(i);
            if (param == null) {
                prepStatement.setObject(i + 1, null);
                continue;
            }
            if (param instanceof java.util.Date) {
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                newSql = replace(newSql,qrepl, "'" + formatter.format((java.util.Date) param) + "'",1);
            } else {
                newSql = replace(newSql,qrepl, "'" + param + "'",1);
            }
            if (param instanceof Integer) {
                prepStatement.setInt(i + 1, ((Integer) param).intValue());
            } else if (param instanceof String) {
                prepStatement.setString(i + 1, (String) param);
            } else if (param instanceof Double) {
                prepStatement.setDouble(i + 1, ((Double) param).doubleValue());
            } else if (param instanceof Float) {
                prepStatement.setFloat(i + 1, ((Float) param).floatValue());
            } else if (param instanceof Long) {
                prepStatement.setLong(i + 1, ((Long) param).longValue());
            } else if (param instanceof Boolean) {
                prepStatement.setBoolean(i + 1, ((Boolean) param).booleanValue());
            } else if (param instanceof java.util.Date) {
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                prepStatement.setString(i + 1, formatter.format((java.util.Date) param));
            } else {
                params.clear();
                throw new Exception("unknown datatype");
            }
        }
        prepStatement.setMaxRows(maxrows);
        prepStatement.setQueryTimeout(query_time_out_sec);

        newSql=replace(newSql, cqrepl, "\\?", -1);
   
        String act=newSql.substring(0,newSql.indexOf(" ")).toLowerCase();

        String OutSqlLevels= ","+ com.mtbu.comm.PropUtil.getInstance("constant").getProperty("SQL_OUT_LEVELS","").toLowerCase().trim()+",";

        if(OutSqlLevels.indexOf(","+act+",")>-1){
            if("select".equals(act)) logger_select.debug(newSql);
            else logger.debug(newSql);
        }
        if (clearParams) {
            params.clear();
        }
    }

    private boolean isInsert(String sql) {
        if (sql.trim().substring(0, 6).equalsIgnoreCase("insert")) return true;
        return false;
    }


    private int[] getPageRecordsNumber(int recordsCount, String iPage, String stripage, int perPageCount) {
        int[] rint = {0, 0, 0};
        int ipage = 1;
        try {
            if (stripage == null || stripage.length() == 0) stripage = iPage;
            ipage = Integer.parseInt(stripage);
        }
        catch (Exception e) {
            ipage = 1;
        }

        if (recordsCount <= 0) {
            return rint;
        }
        int pageCount = (int) Math.ceil((double) recordsCount / perPageCount);
        if (ipage <= 0) ipage = 1;//将ipage保证在合理范围
        if (ipage > pageCount) ipage = pageCount;
        if (perPageCount < recordsCount) rint[0] = 1;
        rint[1] = (ipage - 1) * perPageCount;
        rint[2] = perPageCount;
        if (rint[1] < 0) rint[1] = 0;
        if (rint[1] > recordsCount) rint[1] = recordsCount;
        return rint;
    }

    public static void setDataSourceFactory(DataSourceFactory dataSourceFactory) {
        SQLExecutor.dataSourceFactory = dataSourceFactory;
    }

    private static String replace(String text, String repl, String with, int max) {
        if (text == null || repl == null || with == null || repl.length() == 0 ||
                max == 0) {
            return text;
        }

        StringBuffer buf = new StringBuffer(text.length());
        int start = 0, end = 0;
        while ((end = text.indexOf(repl, start)) != -1) {
            buf.append(text.substring(start, end)).append(with);
            start = end + repl.length();

            if (--max == 0) {
                break;
            }
        }
        buf.append(text.substring(start));
        return buf.toString();
    }
    
    public static void main(String[] args) {
    	String sql = "select *  from(select * from uploadlog__20120701 union all select * from uploadlog__20120702 ) order by id as a;";
        String countsql= "select * " + sql.substring(sql.indexOf("from")).replaceAll("\\*", "count(\\*)");
        countsql = countsql.substring(0,countsql.lastIndexOf("order by"));
        
    	
    	System.out.println(countsql);
	}
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
}